﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
namespace YF.DAL
{
    public class User
    {
        public static bool add(YF.Model.t_user user)
        {
            bool result = false;
            String sql = "insert into t_user(username,password,name,address,sex,mobile,email,qq,state,adddate) values('" + user.Username + "','" + user.Password + "','" + user.Name + "','" + user.Address + "','" + user.Sex + "','" + user.Mobile + "','" + user.Email + "','" + user.Qq + "','" + user.State + "','" + user.Adddate + "')";
            int i = YF.MsSqlHelper.YFMsSqlHelper.ExecuteSql(sql);
            if (i > 0)
            {
                result = true;
            }
            return result;
        }
        public static bool login(string username, string password)
        {
            bool result = false;
            String strsql = " select * from t_user where username='" + username + "'and password='" + password + "'";
            DataTable dataTable = YF.MsSqlHelper.YFMsSqlHelper.Query(strsql).Tables[0];
            if (dataTable.Rows.Count != 0)
            {
                result = true;
            }
            else
            {
                result = false;
            }
            return result;
        }
        public static bool loginorm(string username, string password)
        {
            var db = new PetaPoco.Database("xb");
            var user = db.FirstOrDefault<Model.t_user>("select * from t_user where username=@0 and password=@1", username, password);
            //long count = db.ExecuteScalar<long>("select count(*) from t_user where username=@0 and password=@1", username, password);
            //var user = db.FirstOrDefault<Model.t_user>("where 1=1");
            return user != null;
        }
        
        public static List<YF.Model.t_user> list()
        {
            string strsql = "select *from t_user order by id desc";
            DataTable dt = YF.MsSqlHelper.YFMsSqlHelper.Query(strsql).Tables[0];
            return Dttolist(dt);
        }

        public static List<YF.Model.t_user> Dttolist(DataTable dt)
        {
            List<YF.Model.t_user> list = new List<Model.t_user>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                YF.Model.t_user user = Getuser(int.Parse(dt.Rows[i]["id"].ToString()));

                list.Add(user);
            }
            return list;
        }

        public static bool del(int id)
        {
            bool result = false;
            string strsql = "delete from t_user where id=" + id + "";
            int i = YF.MsSqlHelper.YFMsSqlHelper.ExecuteSql(strsql);
            if (i > 0)
            {
                result = true;
            }
            return result;
        }

        public static bool update(YF.Model.t_user user)
        {
            bool result = false;
            string strsql = "update t_user set username='" + user.Username + "',Name='" + user.Name + "',Address='"
                + user.Address + "',sex='" + user.Sex + "',mobile='" + user.Mobile + "',email='" + user.Email + "',qq='" + user.Qq + "',State=" + user.State + " where id=" + user.Id + " ";
            int i = YF.MsSqlHelper.YFMsSqlHelper.ExecuteSql(strsql);
            if (i > 0)
            {
                result = true;
            }
            return result;
        }

     
        public static YF.Model.t_user Getuser(int id)
        {
            var db = new PetaPoco.Database("xb");
            return db.SingleOrDefault<YF.Model.t_user>("WHERE id=@0", id);
            
        }

        public static bool Search(string username)
        {
            bool result = false;

            String strsql = " select * from t_user where username='" + username + "'";

            DataTable dataTable = YF.MsSqlHelper.YFMsSqlHelper.Query(strsql).Tables[0];
            if (dataTable.Rows.Count == 0)
            {
                result = true;
            }
            else
            {
                result = false;
            }

            return result;


        }

    }
}
